﻿using Dapper;
using FRMS.Helper;
using FRMS.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace FRMS.DAL
{
    public class ElevatorDAL
    {
        public bool AddElevator(ElevatorInfo elevatorInfo)
        {
            string strSql = string.Format("INSERT INTO elevator_info(BuildID,ElevatorName,FloorInfo,pFloorInfo,Workmode,projectID,LadderID)VALUES({0},'{1}','{2}','{3}',{4},{5},{6})", elevatorInfo.BuildID, elevatorInfo.ElevatorName, elevatorInfo.FloorInfo, elevatorInfo.pFloorInfo, 0, elevatorInfo.projectID,elevatorInfo.LadderID);
            return SqlHelper<ElevatorInfo>.SqlExecute(strSql);
        }

        public bool ImportElevator(ElevatorInfo elevatorInfo)
        {
            string strSql = string.Format("INSERT INTO elevator_info(BuildID,ElevatorName,FloorInfo,pFloorInfo,Workmode,projectID)VALUES((SELECT ID FROM building_info WHERE BuildingName='{0}' AND projectID={5}),'{1}','{2}','{3}',{4},{5})", elevatorInfo.BuildingName, elevatorInfo.ElevatorName, elevatorInfo.FloorInfo, elevatorInfo.pFloorInfo, elevatorInfo.Workmode, elevatorInfo.projectID);
            return SqlHelper<ElevatorInfo>.SqlExecute(strSql);
        }

        public bool UpdateElevator(ElevatorInfo elevatorInfo)
        {
            string strSql = string.Format("UPDATE elevator_info SET ElevatorName='{0}',BuildID={2},FloorInfo='{3}',pFloorInfo='{4}',LadderID={5} WHERE ID={1}", elevatorInfo.ElevatorName, elevatorInfo.ID, elevatorInfo.BuildID, elevatorInfo.FloorInfo, elevatorInfo.pFloorInfo,elevatorInfo.LadderID);
            return SqlHelper<ElevatorInfo>.SqlExecute(strSql);
        }

        public bool DelElevator(ElevatorInfo elevatorInfo)
        {
            string strSql = "UPDATE elevator_info SET IsDelete=1 WHERE ID=" + elevatorInfo.ID;
            return SqlHelper<ElevatorInfo>.SqlExecute(strSql);
        }

        public List<ElevatorInfo> QueryElevatorInfo(ElevatorInfo elevatorInfo)
        {
            string strSql = "SELECT * FROM elevator_info";
            string sqlwhere = string.Empty;

            if (elevatorInfo.projectID > 0)
            {
                sqlwhere += " projectID=" + elevatorInfo.projectID + " AND";
            }
            if (elevatorInfo.BuildID > 0)
            {
                sqlwhere += " BuildID=" + elevatorInfo.BuildID + " AND";
            }
            sqlwhere += " IsDelete=0 AND";

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            if (elevatorInfo.Recordhead > -1 && elevatorInfo.Recordtail > 0)
            {
                strSql += " LIMIT " + elevatorInfo.Recordhead + "," + elevatorInfo.Recordtail;
            }

            return SqlHelper<ElevatorInfo>.SqlList(strSql);
        }

        public int QueryElevatorInfoCount(ElevatorInfo elevatorInfo)
        {
            string strSql = "SELECT COUNT(1) FROM elevator_info";
            string sqlwhere = string.Empty;

            if (elevatorInfo.projectID > 0)
            {
                sqlwhere += " projectID=" + elevatorInfo.projectID + " AND";
            }
            if (elevatorInfo.BuildID > 0)
            {
                sqlwhere += " BuildID=" + elevatorInfo.BuildID + " AND";
            }
            sqlwhere += " IsDelete=0 AND";

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            return SqlHelper<ElevatorInfo>.SqlScalar(strSql);
        }

        public bool CheckDevice(ElevatorInfo elevatorInfo)
        {
            string strSql = "SELECT COUNT(1) FROM device_info where ElevatorID=" + elevatorInfo.ID + " AND IsDelete=0";

            int count = SqlHelper<ElevatorInfo>.SqlScalar(strSql);
            if (count > 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public List<ElevatorInfo> QueryElevatorList(ElevatorInfo elevatorInfo)
        {
            string strSql = "SELECT * FROM vi_elevator";
            string sqlwhere = string.Empty;

            if (elevatorInfo.projectID > 0)
            {
                sqlwhere += " projectID=" + elevatorInfo.projectID + " AND";
            }
            if (elevatorInfo.BuildID > 0)
            {
                sqlwhere += " BuildID=" + elevatorInfo.BuildID + " AND";
            }
            if (!string.IsNullOrEmpty(elevatorInfo.ElevatorName))
            {
                sqlwhere += " ElevatorName like '%" + elevatorInfo.ElevatorName + "%' AND";
            }
            if (!string.IsNullOrEmpty(elevatorInfo.BuildingName))
            {
                sqlwhere += " BuildingName like '%" + elevatorInfo.BuildingName + "%' AND";
            }
            sqlwhere += " IsDelete=0 AND";

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            if (elevatorInfo.Recordhead > -1 && elevatorInfo.Recordtail > 0)
            {
                strSql += " LIMIT " + elevatorInfo.Recordhead + "," + elevatorInfo.Recordtail;
            }

            return SqlHelper<ElevatorInfo>.SqlList(strSql);
        }

        public int QueryElevatorListCount(ElevatorInfo elevatorInfo)
        {
            string strSql = "SELECT COUNT(1) FROM vi_elevator";
            string sqlwhere = string.Empty;

            if (elevatorInfo.projectID > 0)
            {
                sqlwhere += " projectID=" + elevatorInfo.projectID + " AND";
            }
            if (elevatorInfo.BuildID > 0)
            {
                sqlwhere += " BuildID=" + elevatorInfo.BuildID + " AND";
            }
            if (!string.IsNullOrEmpty(elevatorInfo.ElevatorName))
            {
                sqlwhere += " ElevatorName like '%" + elevatorInfo.ElevatorName + "%' AND";
            }
            if (!string.IsNullOrEmpty(elevatorInfo.BuildingName))
            {
                sqlwhere += " BuildingName like '%" + elevatorInfo.BuildingName + "%' AND";
            }
            sqlwhere += " IsDelete=0 AND";

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            return SqlHelper<ElevatorInfo>.SqlScalar(strSql);
        }

        public ElevatorInfo LoadElevator(int id)
        {
            string strSql = "SELECT * FROM elevator_info WHERE ID=" + id;
            return SqlHelper<ElevatorInfo>.SqlSingleData(strSql);
        }

        public int CheckElevator(int buildid, string ele_name, int id)
        {
            string strSql = "SELECT * FROM elevator_info WHERE BuildID=" + buildid + " AND ElevatorName='" + ele_name + "' AND ID!=" + id + " AND IsDelete=0";
            return SqlHelper<ElevatorInfo>.SqlScalar(strSql);
        }

        public bool UpdateMode(ElevatorInfo elevatorInfo)
        {
            string strSql = string.Format("UPDATE elevator_info SET Workmode={0} WHERE ID={1}", elevatorInfo.Workmode, elevatorInfo.ID);
            return SqlHelper<ElevatorInfo>.SqlExecute(strSql);
        }

        //public int QueryElevatorWorkmode(int deviceInfo)
        //{
        //    string strS = "select Workmode from elevator_info where ID= '" + deviceInfo + "'";
        //    return SqlHelper<DeviceInfo>.SqlScalar(strS);
        //}
        public List<ElevatorInfo> QueryElevatorInf(int eid)
        {
            string strSql = "select * from elevator_info where ID= '" + eid + "'";
            return SqlHelper<ElevatorInfo>.SqlList(strSql);
        } 
        public bool UpdateMode(string ele_name,string build_name,int mode)
        {
            string strSql = string.Format("UPDATE elevator_info SET Workmode={0} WHERE ElevatorName='{1}' AND BuildID=(SELECT ID FROM building_info WHERE BuildingName='{2}' AND IsDelete=0 LIMIT 0,1)", mode, ele_name,build_name);
            return SqlHelper<ElevatorInfo>.SqlExecute(strSql);
        }
    }
}
